package com.iou.database;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import com.iou.bean.IOUNote;
import com.iou.bean.IOUUser;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.jdbc.JdbcConnectionSource;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

/**
 * Class to handle Database connections and queries.
 */
public class DatabaseHelper {

    private static final String databaseUrl = "jdbc:h2:mem:account";
    public static Dao<IOUNote, Integer> noteDao;
    public static Dao<IOUUser, String> userDao;
    public static ConnectionSource connectionSource;

    /**
     * Initialize.
     * 
     * @throws Exception
     */
    public static void init() throws Exception {

        // create a connection source to a database
        getDatabaseConnection();

        // instantiate the daos
        noteDao = DaoManager.createDao(connectionSource, IOUNote.class);
        userDao = DaoManager.createDao(connectionSource, IOUUser.class);

        // create the 'IOUNote' table if doesn't exist
        TableUtils.createTableIfNotExists(connectionSource, IOUNote.class);

        // create the 'IOUUser' table if doesn't exist
        TableUtils.createTableIfNotExists(connectionSource, IOUUser.class);

    }

    /**
     * Stores an IOUNote object in the DB.
     * 
     * @param note
     * @throws SQLException
     */

    public static void storeNote(IOUNote note) throws SQLException {
        getDatabaseConnection();
        noteDao.createOrUpdate(note);
        System.out.println("Varastoitiin note " + note.toString());
    }

    /**
     * Queries an IOUNote object from the DB.
     * 
     * @param id
     * @return
     * @throws SQLException
     */
    public static IOUNote getNote(Integer id) throws SQLException {
        getDatabaseConnection();
        return noteDao.queryForId(id);
    }

    /**
     * Stores an IOUUser object in the DB.
     * 
     * @param user
     * @throws SQLException
     */
    public static void storeUser(IOUUser user) throws SQLException {
        getDatabaseConnection();
        userDao.createOrUpdate(user);
        System.out.println("Varastoitiin user " + user.toString());
    }

    /**
     * Queries an IOUUSer object from the DB.
     * 
     * @param username
     * @return
     * @throws SQLException
     */
    public static IOUUser getUser(String username) throws SQLException {
        getDatabaseConnection();
        return userDao.queryForId(username);
    }

    /**
     * Removes an IOUNote from the DB.
     * 
     * @param note
     * @throws SQLException
     */
    public static void deleteNote(IOUNote note) throws SQLException {
        getDatabaseConnection();
        noteDao.delete(note);
        System.out.println("Tuhottiin note: " + note.toString());
    }

    /**
     * Checks if a username-password pair is valid. Returns null if not.
     * 
     * @param username
     * @param password
     * @return IOUUser
     * @throws SQLException
     */
    public static IOUUser validateUser(String username, String password)
            throws SQLException {
        getDatabaseConnection();
        IOUUser user = null;
        user = userDao.queryForId(username);
        if (user != null) {
            // if (user.getPassword() == password) {
            if (user.getPassword().compareTo(password) == 0) {
                return user;
            } else {
                return null;
            }
        } else {
            return null;
        }
    }

    /**
     * Establish the DB connection.
     * 
     * @return
     * @throws SQLException
     */
    private static ConnectionSource getDatabaseConnection() throws SQLException {
        if (connectionSource == null) {
            connectionSource = new JdbcConnectionSource(databaseUrl);
        }
        return connectionSource;
    }

    /**
     * Prints all of the notes in the DB on the console.
     */
    public static void printNotesOnConsole() {
        List<IOUNote> queryResult = null;
        try {
            queryResult = noteDao.queryForAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        for (int i = 0; i < queryResult.size(); i++) {
            System.out.println(queryResult.get(i));
        }
    }

    /**
     * Prints all of the users in the DB on the console.
     */
    public static void printUsersOnConsole() {
        List<IOUUser> queryResult = null;
        try {
            queryResult = userDao.queryForAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        for (int i = 0; i < queryResult.size(); i++) {
            System.out.println(queryResult.get(i));
        }
    }

    public static void buildMockData() throws SQLException {
        getDatabaseConnection();
        IOUUser kayttaja = new IOUUser("TeppoTestaaja", "teppo");
        storeUser(kayttaja);

        ArrayList<String> nimia = new ArrayList<String>();
        nimia.add("Teppo");
        nimia.add("Seppo");
        nimia.add("Mika");
        nimia.add("Matti");
        nimia.add("Kirsi");
        nimia.add("Sirpa");
        nimia.add("Sanelma");
        nimia.add("Pertti");
        nimia.add("Mom");
        nimia.add("Tapani");

        ArrayList<String> kuvailuja = new ArrayList<String>();
        kuvailuja.add("beer");
        kuvailuja.add("Subway");
        kuvailuja.add("clothes");
        kuvailuja.add("food");
        kuvailuja.add("milk");
        kuvailuja.add("car");

        Random rd = new Random();
        int nimi = 0;
        int kuvailu = 0;
        double maara = 0.0;
        int iou = 0;
        int complete = 0;
        int paiva = 0;
        int kuukausi = 0;
        for (int i = 0; i < 50; i++) {
            nimi = rd.nextInt(nimia.size());
            kuvailu = rd.nextInt(kuvailuja.size());
            maara = rd.nextInt(200);
            maara = maara / 2;
            paiva = rd.nextInt(27);
            kuukausi = rd.nextInt(12);

            iou = rd.nextInt(3);
            complete = rd.nextInt(8);

            IOUNote noteToAdd = new IOUNote("IOU", maara, nimia.get(nimi),
                    kuvailuja.get(kuvailu), paiva + 1, kuukausi + 1, 2012,
                    false);

            noteToAdd.setUser(kayttaja);
            if (iou == 2) {
                noteToAdd.setType("IOUMe");
            } else {
                noteToAdd.setType("IOU");

            }
            if (complete > 5) {
                noteToAdd.setStatus(true);
            }

            storeNote(noteToAdd);
        }

    }
}